package it.unibz.easytravel.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.location.Location;
import android.util.Log;

public class PlacemarkDbAdapter {

	private static final String TAG = "PlacemarksDb";
	
	private static final String TRAVEL_ID = "travel_id";
	private static final String TABLE_NAME = "placemark";
	private static final String NAME = "name";
	private static final String DESCRIPTION= "description";
	private static final String ID = "_id";
	
	private DatabaseHelper mDbHelper;

	private SQLiteDatabase mDb;

	private final Context mCtx;

	/**
	 * Database creation sql statement
	 */
	private static final String DATABASE_CREATE = 
		"create table " + TABLE_NAME + " ("
			+ ID+" integer primary key autoincrement, "
			+ TRAVEL_ID+" integer, "
			+ NAME +" text,"
			+ DESCRIPTION + " text,"
			+ "latitude text,"
			+ "longitude text,"
			+ "altitude text," 
			+ "time text," + "picture text);";

	public static void onCreate(SQLiteDatabase db) {
		db.execSQL(DATABASE_CREATE);
	}

	public static void onUpgrade(SQLiteDatabase db) {
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
		onCreate(db);
	}

	public PlacemarkDbAdapter(Context ctx) {
		this.mCtx = ctx;
	}

	public PlacemarkDbAdapter open() throws SQLException {
		mDbHelper = new DatabaseHelper(mCtx);
		mDb = mDbHelper.getWritableDatabase();
		return this;
	}

	public void close() {
		mDbHelper.close();
	}

	public void delete() {
		Log.i(TAG, "deleteTable");
		mDb.execSQL("DELETE FROM " + TABLE_NAME);
	}

	public long createPlacemark(int travelId, String name, String description, Location loc, String pic) {
		Log.i(TAG, "createNewCoordinateEntry");
		ContentValues values = new ContentValues();
		values.put(TRAVEL_ID, travelId);
		values.put(NAME, name);
		values.put(DESCRIPTION, description);
		values.put("latitude", loc.getLatitude());
		values.put("longitude", loc.getLongitude());
		values.put("altitude", loc.getAltitude());
		values.put("time", loc.getTime());
		values.put("picture", pic);
		return mDb.insert(TABLE_NAME, null, values);
	}
	
	public long createPlacemark(int travelId, String name, String description, String latitude, String longitude, String altitude, String time, String pic) {
		Log.i(TAG, "createNewCoordinateEntry");
		ContentValues values = new ContentValues();
		values.put(TRAVEL_ID, travelId);
		values.put(NAME, name);
		values.put(DESCRIPTION, description);
		values.put("latitude", latitude);
		values.put("longitude", longitude);
		values.put("altitude", altitude);
		values.put("time", time);
		values.put("picture", pic);
		return mDb.insert(TABLE_NAME, null, values);
	}

	/**
	 * SLQ = UPDATE placemark SET name = name AND description = description 
	 * WHERE node_id = id
	 * 
	 * @param userId
	 * @param travelId
	 * @param moneyFlow
	 */
	public void updatePlacemarkInfo(String name, String description, int id) {
		Log.i(TAG, "updatePlacemarkInfo "+name+" "+description+ " "+id);
		String where = ID+"=" + id;
		ContentValues values = new ContentValues();
		values.put(NAME, name);
		values.put(DESCRIPTION, description);
		mDb.update(TABLE_NAME, values, where, null);
	}
	
	/**
	 * SQL = SELECT _id, travel_id, x, y, z FROM coordinates
	 * 
	 * @return cursor to result list
	 */
	public Cursor getPlacemarks() {
		Log.i(TAG, "getAllPlacemarks");
		return mDb.query(TABLE_NAME, new String[] { "_id", TRAVEL_ID, "name", "description",
				"latitude", "longitude", "altitude", "time", "picture" }, null, null, null, null, null);
	}

	public Cursor getPlacemarks(int travelId) {
		Log.i(TAG, "getAllPlacemarks(travel_id)");
		String where = TRAVEL_ID + "=" + travelId;
		return mDb.query(TABLE_NAME, new String[] { "_id", TRAVEL_ID, "name", "description",
				"latitude", "longitude", "altitude", "time", "picture" }, where, null, null, null, null);
	}
}
